-- @owner: lvlintao666
-- @date: 2024/05/13
-- @testpoint: 在order by中使用prior递归查询
--step1:创建间隔分区表，插入数据  expect:建表成功，插入数据成功
drop table if exists t_prior_0005;
create table t_prior_0005
(pid int,
id int,
name varchar(100),
"int1" tinyint,
"int2" smallint,
"int4" integer,
"int8" bigint,
"float4" float4,
"float8" float8,
"numeric" decimal(20, 6),
"bit4" bit(4),
"boolean" boolean,
"date" date,
"time" time,
"timetz" timetz,
"timestamp" timestamp,
"timestamptz" timestamptz,
"char" char(100),
"varchar" varchar(100),
"blob" blob,
"text" text
);

insert into t_prior_0005 values (0,1,'node1',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');
insert into t_prior_0005 values (1,2,'node2',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');
insert into t_prior_0005 values (1,3,'node3',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');
insert into t_prior_0005 values (2,4,'node4',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');
insert into t_prior_0005 values (2,5,'node5',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');
insert into t_prior_0005 values (3,6,'node6',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');
insert into t_prior_0005 values (3,7,'node7',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');

--step2 在order by中使用prior查询出正确结果;expect:成功
select count(id) from t_prior_0005 start with id = 1 connect by prior id = pid group by prior name order by prior name;

--step3 清理环境;expect:成功
drop table if exists t_prior_0005 cascade;